﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace ShaboForm
{
    class DataAccessLayer
    {

        //my functions
        public static DataTable FindEmployee(string employeeID)
        {
            try
            {
                string query = "Select ID,FirstName, LastName, Permisions from tblEmployee where ID="+employeeID+"";

                DataTable dt = AdoHelper.ExecuteDataTable(query);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void AddWorker(string id, string fn, string ln, string jb, string st, string hn, string ct, string pn, string pr)
        {
            try
            {
                string query = "Insert into tblEmployee values('" + id + "', '" + fn + "', '" + ln + "', '" + jb + "', '" + st + "', '" + hn + "', '" + ct + "', '" + pn + "', '" + pr + "')";
                AdoHelper.ExecuteNonQuery(query);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable GetAllEmployees()
        {
            try
            {
                string query = "Select * from tblEmployee";
                DataTable dt = AdoHelper.ExecuteDataTable(query);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable SearchEmployee(string searchWord, string searchBy)
        {
            try
            {
                string query = "Select * from tblEmployee where " + searchWord + "=" + searchBy + "";
                DataTable dt = AdoHelper.ExecuteDataTable(query);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //old project functions

        public static DataTable GetAlltblCarOwners()
        {
            try
            {
                string query = "Select * from tblCarOwners";

                DataTable dt = AdoHelper.ExecuteDataTable(query);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable GetAlltblShibutz()
        {
            try
            {
                string query = "Select * from tblShibutz";

                DataTable dt = AdoHelper.ExecuteDataTable(query);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable GetCars()
        {
            try
            {
                string query = "Select CarNum from tblCars";

                DataTable dt = AdoHelper.ExecuteDataTable(query);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void RemoveCar(string numcar)
        {
            try
            {
                string query = "Delete from tblCars where CarNum='" + numcar + "'";
                AdoHelper.ExecuteNonQuery(query);
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public static DataTable GetCarOwners()
        {
            try
            {
                string query = "Select EmployeeID from tblCarOwners";

                DataTable dt = AdoHelper.ExecuteDataTable(query);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void RemoveEmployee(string employeeid)
        {
            try
            {
                string query = "Delete from tblCarOwners where EmployeeID='" + employeeid + "'";
                AdoHelper.ExecuteNonQuery(query);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void AddNewCarOwners(string employeeid, string fname, string lname, string sex, string carnum)
        {
            string query = "Insert into tblCarOwners values('" + employeeid + "' , '" + fname + "' , '" + lname + "', '" + sex + "', '" + carnum + "')";
            AdoHelper.ExecuteNonQuery(query);
        }

        public static void AddNewCar(string numcar, string status, string comapny, string date, string use)
        {
            string query = "Insert into tblCars values('" + numcar + "' , '" + status + "' , '" + comapny + "', '" + date + "', '" + use + "')";
            AdoHelper.ExecuteNonQuery(query);
        }

        public static void AddShibutz(string employeeid, string carnum)
        {
            string query = "Insert into tblShibutz (EmployeeID, CarNum)  values('" + employeeid + "' , '" + carnum + "')";
            AdoHelper.ExecuteNonQuery(query);
        }

        public static DataTable GetShibutz()
        {
            try
            {
                string query = "Select ShibutzCode from tblShibutz";

                DataTable dt = AdoHelper.ExecuteDataTable(query);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void AddEvent(string shibutz, string date, string place, string description)
        {
            string query = "Insert into tblEvents (ShibutzCode, EventDate, place, Description) values('" + shibutz + "','" + date + "','" + place + "','" + description + "')";
            AdoHelper.ExecuteNonQuery(query);

        }

        public static void AddAccident(string status, string sum)
        {
            string query = "Insert into tblAccidents (Status, SumOfPayment) values('" + status + "','" + sum + "')";
            AdoHelper.ExecuteNonQuery(query);
        }

        public static void AddReport(string sum)
        {
            string query = "Insert into tblPoliceReport (SumOfPayment) values('" + sum + "')";
            AdoHelper.ExecuteNonQuery(query);
        }

        public static DataTable CountEvent()
        {
            try
            {
                string query = "SELECT * from qry_2";

                DataTable dt = AdoHelper.ExecuteDataTable(query);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public static DataTable CarNotInUse()
        {
            try
            {
                string query = "SELECT * from qry_3";

                DataTable dt = AdoHelper.ExecuteDataTable(query);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public static DataTable CountCars()
        {
            try
            {
                string query = "SELECT * from qry_4";

                DataTable dt = AdoHelper.ExecuteDataTable(query);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public static DataTable GetAlltblCars()
        {
            try
            {
                string query = "SELECT * from tblCars";

                DataTable dt = AdoHelper.ExecuteDataTable(query);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable GetAlltblEvents()
        {
            try
            {
                string query = "SELECT * from tblEvents";

                DataTable dt = AdoHelper.ExecuteDataTable(query);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }


}


